from demo.init import db

'''
多对多关系
假设一堆学生学习不同的课程，这就是多对多关系
'''
tb_student_course = db.Table('tb_student_course',
                             db.Column('student_id', db.Integer, db.ForeignKey('student.id')),
                             db.Column('course_id', db.Integer, db.ForeignKey('courses.id')))

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)

    # 关联属性，多对多的情况，可以写在任意一个模型类型中，含有正向引用和反向引用
    relate_courses = db.relationship('Course',
                                     secondary=tb_student_course,
                                     backref='relate_student',
                                     lazy='dynamic')
    def __repr__(self):
        return '<Student %r>' % self.name

class Course(db.Model):
    __tablename__ = 'courses'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)

    def __repr__(self):
        return '<Course %r>' % self.name

# 创建所有表
db.create_all()

# 删除Student表和Course表的所有行
Student.query.delete()
Course.query.delete()

# 添加数据
stu1 = Student(name='张三')
stu2 = Student(name='李四')
stu3 = Student(name='王五')

cou1 = Course(name='物理')
cou2 = Course(name='化学')
cou3 = Course(name='生物')

# 添加关系
stu1.relate_courses = [cou2, cou3]
stu2.relate_courses = [cou2]
stu3.relate_courses = [cou1, cou2, cou3]

# 添加到数据库
db.session.add_all([stu1, stu2, stu3, cou1, cou2, cou3])
db.session.commit()

# 查询张三学习的所有课程
stu = Student.query.filter_by(name='张三').first()
for course in stu.relate_courses:
    print('张三学习的课程：', course.name)

# 查询学习生物这门课程的所有学生
course = Course.query.filter_by(name='生物').first()
for student in course.relate_student:
    print('学习生物的学生', student.name)